﻿/*=================================================================
* 版权信息：Copyright (C) 2005-2011 捷点科技 All Rights Reserved.
* 文件名称：CNVP_PostAddress.cs
* 创建标识：曾乃强 15158756652 2011-04-21 01:04:15
* 修改标识：
* 功能描述：
===================================================================*/
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using CNVP.Framework.Helper;
using CNVP.Config;

namespace CNVP.JShop.DAL
{
    /// <summary>
    /// 对*CNVP_PostAddress*操作数据库(针对SQL Server)的实现
    /// </summary>
    public class CNVP_PostAddress
    {
       private string Prefix = DBConfig.Prefix;
        #region DAL
        /// <summary>
        /// 添加
        /// </summary>
        public int Insert(Model.CNVP_PostAddress obj)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("insert into  " + Prefix + "PostAddress ([PostTitle],[PostTrueName],[Sex],[Province],[City],[Area],[PostAddress],[ZipCode],[MobilePhone],[Phone],[Text],[IsTolerant])");
            sql.Append("values(@PostTitle,@PostTrueName,@Sex,@Province,@City,@Area,@PostAddress,@ZipCode,@MobilePhone,@Phone,@Text,@IsTolerant)");
            
            SqlParameter[] pra = {
                            new SqlParameter("@PostTitle",obj.PostTitle),
							new SqlParameter("@PostTrueName",obj.PostTrueName),
							new SqlParameter("@Sex",obj.Sex),
							new SqlParameter("@Province",obj.Province),
							new SqlParameter("@City",obj.City),
							new SqlParameter("@Area",obj.Area),
							new SqlParameter("@PostAddress",obj.PostAddress),
							new SqlParameter("@ZipCode",obj.ZipCode),
							new SqlParameter("@MobilePhone",obj.MobilePhone),
							new SqlParameter("@Phone",obj.Phone),
							new SqlParameter("@Text",obj.Text),
							new SqlParameter("@IsTolerant",obj.IsTolerant)
                                 };
            return DbHelper.ExecuteNonQuery(sql.ToString(), pra);
        }
        /// <summary>
        /// 修改
        /// </summary>
        public int Update(Model.CNVP_PostAddress obj)
        {
             StringBuilder sql = new StringBuilder();
             sql.Append("update " + Prefix + "PostAddress set [PostTitle] = @PostTitle,[PostTrueName] = @PostTrueName,[Sex] = @Sex,[Province] = @Province,[City] = @City,[Area] = @Area,[PostAddress] = @PostAddress,[ZipCode] = @ZipCode,[MobilePhone] = @MobilePhone,[Phone] = @Phone,[Text] = @Text,[IsTolerant] = @IsTolerant where [ID] = @ID");

            SqlParameter[] pra = { 
                            new SqlParameter("@ID",obj.ID),
							new SqlParameter("@PostTitle",obj.PostTitle),
							new SqlParameter("@PostTrueName",obj.PostTrueName),
							new SqlParameter("@Sex",obj.Sex),
							new SqlParameter("@Province",obj.Province),
							new SqlParameter("@City",obj.City),
							new SqlParameter("@Area",obj.Area),
							new SqlParameter("@PostAddress",obj.PostAddress),
							new SqlParameter("@ZipCode",obj.ZipCode),
							new SqlParameter("@MobilePhone",obj.MobilePhone),
							new SqlParameter("@Phone",obj.Phone),
							new SqlParameter("@Text",obj.Text),
							new SqlParameter("@IsTolerant",obj.IsTolerant)
                                };
             return DbHelper.ExecuteNonQuery(sql.ToString(),pra);
        }
        /// <summary>
        /// 删除
        /// </summary>
        public int Delete(Model.CNVP_PostAddress obj)
        {
           StringBuilder sql = new StringBuilder();
           sql.Append("delete from " + Prefix + "PostAddress where [ID] = @ID");
           SqlParameter[] pra = {
                                new SqlParameter("@ID",obj.ID)
						
                                };
           return DbHelper.ExecuteNonQuery(sql.ToString(),pra);
        }
        /// <summary>
        /// 查看单个
        /// </summary>
        public Model.CNVP_PostAddress LoadById(int ID)
        {
            
            StringBuilder sql = new StringBuilder();
            sql.Append("select [PostTitle],[PostTrueName],[Sex],[Province],[City],[Area],[PostAddress],[ZipCode],[MobilePhone],[Phone],[Text],[IsTolerant] from " + Prefix + "PostAddress");
            sql.Append(" where [ID] = @ID");
            SqlParameter[] pra = {
                                 new SqlParameter("@ID",ID)
						
                                    };
            DataTable DT = DbHelper.ExecuteTable(sql.ToString(),pra);
            Model.CNVP_PostAddress cnvp_postaddress = new Model.CNVP_PostAddress();
            if (DT != null && DT.Rows.Count > 0)
            {
                DataRow dr = DT.Rows[0];
                cnvp_postaddress.PostTitle = dr["PostTitle"] is DBNull ? String.Empty : (string)dr["PostTitle"];
                cnvp_postaddress.PostTrueName = dr["PostTrueName"] is DBNull ? String.Empty : (string)dr["PostTrueName"];
                cnvp_postaddress.Sex = dr["Sex"] is DBNull ? (byte)0 : (byte)dr["Sex"];
                cnvp_postaddress.Province = dr["Province"] is DBNull ? String.Empty : (string)dr["Province"];
                cnvp_postaddress.City = dr["City"] is DBNull ? String.Empty : (string)dr["City"];
                cnvp_postaddress.Area = dr["Area"] is DBNull ? String.Empty : (string)dr["Area"];
                cnvp_postaddress.PostAddress = dr["PostAddress"] is DBNull ? String.Empty : (string)dr["PostAddress"];
                cnvp_postaddress.ZipCode = dr["ZipCode"] is DBNull ? String.Empty : (string)dr["ZipCode"];
                cnvp_postaddress.MobilePhone = dr["MobilePhone"] is DBNull ? String.Empty : (string)dr["MobilePhone"];
                cnvp_postaddress.Phone = dr["Phone"] is DBNull ? String.Empty : (string)dr["Phone"];
                cnvp_postaddress.Text = dr["Text"] is DBNull ? String.Empty : (string)dr["Text"];
                cnvp_postaddress.IsTolerant = dr["IsTolerant"] is DBNull ? (byte)0 : (byte)dr["IsTolerant"];
            } 
            return cnvp_postaddress;
        }
        /// <summary>
        /// 查看全部
        /// </summary>
        public List<Model.CNVP_PostAddress> LoadAll()
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select [ID],[PostTitle],[PostTrueName],[Sex],[Province],[City],[Area],[PostAddress],[ZipCode],[MobilePhone],[Phone],[Text],[IsTolerant] from " + Prefix + "PostAddress Order by [IsTolerant] desc ");//排序“设置为默认地址”
            DataTable DT = DbHelper.ExecuteTable(sql.ToString());
            List<Model.CNVP_PostAddress> list=new List<Model.CNVP_PostAddress>();
            if (DT != null && DT.Rows.Count > 0)
            {
                foreach (DataRow dr in DT.Rows)
                {
                    Model.CNVP_PostAddress cnvp_postaddress = new Model.CNVP_PostAddress();
                    cnvp_postaddress.ID = dr["ID"] is DBNull ? 0 : (int)dr["ID"];
                    cnvp_postaddress.PostTitle = dr["PostTitle"] is DBNull ? String.Empty : (string)dr["PostTitle"];
                    cnvp_postaddress.PostTrueName = dr["PostTrueName"] is DBNull ? String.Empty : (string)dr["PostTrueName"];
                    cnvp_postaddress.Sex = dr["Sex"] is DBNull ? (byte)0 : (byte)dr["Sex"];
                    cnvp_postaddress.Province = dr["Province"] is DBNull ? String.Empty : (string)dr["Province"];
                    cnvp_postaddress.City = dr["City"] is DBNull ? String.Empty : (string)dr["City"];
                    cnvp_postaddress.Area = dr["Area"] is DBNull ? String.Empty : (string)dr["Area"];
                    cnvp_postaddress.PostAddress = dr["PostAddress"] is DBNull ? String.Empty : (string)dr["PostAddress"];
                    cnvp_postaddress.ZipCode = dr["ZipCode"] is DBNull ? String.Empty : (string)dr["ZipCode"];
                    cnvp_postaddress.MobilePhone = dr["MobilePhone"] is DBNull ? String.Empty : (string)dr["MobilePhone"];
                    cnvp_postaddress.Phone = dr["Phone"] is DBNull ? String.Empty : (string)dr["Phone"];
                    cnvp_postaddress.Text = dr["Text"] is DBNull ? String.Empty : (string)dr["Text"];
                    cnvp_postaddress.IsTolerant = dr["IsTolerant"] is DBNull ? (byte)0 : (byte)dr["IsTolerant"];
                    list.Add(cnvp_postaddress);
                }
            }
            return list;

        }
        /// <summary>
        /// 分页
        /// </summary>
        public DataTable GetPage(string PageName, int PageIndex, int PageSize, out int RecordCount, out int PageCount, params SQLConditionInfo[] SqlCondition)
        {
            try
            {
                string AllFields = "[ID],[PostTitle],[PostTrueName],[Sex],[Province],[City],[Area],[PostAddress],[ZipCode],[MobilePhone],[Phone],[Text],[IsTolerant]";
                string Condition = "" + Prefix + "PostAddress";
                string IndexField = "ID";
                string OrderFields = "order by ID Desc";
                return DbHelper.ExecutePage(AllFields, Condition, IndexField, OrderFields, PageIndex, PageSize, out RecordCount, out PageCount, null);
            }
            catch (Exception e)
            {
                throw new Exception("用于分页的SQL语句无效:" + e.Message);
            }

        }
        #endregion

        #region 设置默认时
        /// <summary>
        /// 把为‘1’全部更新设置成‘0’
        /// </summary>
        public int UpdateWhereOne()
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("update " + Prefix + "PostAddress set [IsTolerant] = 0 where [IsTolerant] = 1");
            return DbHelper.ExecuteNonQuery(sql.ToString());
        }

        /// <summary>
        /// 查看发货信息
        /// </summary>
        public Model.CNVP_PostAddress LoadDeliveryInfoById(int ID)
        {

            StringBuilder sql = new StringBuilder();
            sql.Append("select [PostTitle],[PostTrueName],[Sex],[PostAddress],[ZipCode],[MobilePhone],[Phone],[Text],[IsTolerant],");
            sql.Append(" (select Name from " + Prefix + "Province where Code = [Province]) as Province_Name, (select Name from  " + Prefix + "City where Code= [City]) as City_Name , (select Name from " + Prefix + "Area where Code=[Area]) as Area_Name from " + Prefix + "PostAddress");
            sql.Append(" where [ID] = @ID");
            SqlParameter[] pra = {
                                 new SqlParameter("@ID",ID)
						
                                    };
            DataTable DT = DbHelper.ExecuteTable(sql.ToString(), pra);
            Model.CNVP_PostAddress cnvp_postaddress = new Model.CNVP_PostAddress();
            if (DT != null && DT.Rows.Count > 0)
            {
                DataRow dr = DT.Rows[0];
                cnvp_postaddress.PostTitle = dr["PostTitle"] is DBNull ? String.Empty : (string)dr["PostTitle"];
                cnvp_postaddress.PostTrueName = dr["PostTrueName"] is DBNull ? String.Empty : (string)dr["PostTrueName"];
                cnvp_postaddress.Sex = dr["Sex"] is DBNull ? (byte)0 : (byte)dr["Sex"];
                cnvp_postaddress.Province = dr["Province_Name"] is DBNull ? String.Empty : (string)dr["Province_Name"];
                cnvp_postaddress.City = dr["City_Name"] is DBNull ? String.Empty : (string)dr["City_Name"];
                cnvp_postaddress.Area = dr["Area_Name"] is DBNull ? String.Empty : (string)dr["Area_Name"];
                cnvp_postaddress.PostAddress = dr["PostAddress"] is DBNull ? String.Empty : (string)dr["PostAddress"];
                cnvp_postaddress.ZipCode = dr["ZipCode"] is DBNull ? String.Empty : (string)dr["ZipCode"];
                cnvp_postaddress.MobilePhone = dr["MobilePhone"] is DBNull ? String.Empty : (string)dr["MobilePhone"];
                cnvp_postaddress.Phone = dr["Phone"] is DBNull ? String.Empty : (string)dr["Phone"];
                cnvp_postaddress.Text = dr["Text"] is DBNull ? String.Empty : (string)dr["Text"];
                cnvp_postaddress.IsTolerant = dr["IsTolerant"] is DBNull ? (byte)0 : (byte)dr["IsTolerant"];

                
            }
            return cnvp_postaddress;
        }
        #endregion
    }
}

